package com.eduction.modules.system.dao;

import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.eduction.bean.ItemListExpertBean;
import com.eduction.common.dbutil.DbHelper;
import com.eduction.modules.system.entity.ExpertItemMap;

@Repository
public class ExpertItemMapRepository {

	@Autowired
	private DbHelper dbHelper;
	@Autowired
	private ExpertItemMapDao expertItemMapDao;
	
	/**
	 * 保存专家与指标的关系
	 */
	public void saveExpertItemMap(ExpertItemMap e){
		expertItemMapDao.save(e);
	}
	
	/**
	 * 根据学校id查询信息
	 * @throws SQLException 
	 */
	public List<ExpertItemMap> queryInfoBySchoolId(String schoolId,String type) throws SQLException{
		String sql = "SELECT * FROM t_expert_item_map where s_school_id = ? and s_type=?";
		return dbHelper.getBeanList(sql, ExpertItemMap.class, schoolId,type);
		
	}
	
	/**
	 * 根据专家id查询分项表
	 * @throws SQLException 
	 */
	public List<ItemListExpertBean> queryListByExpertId(String expertId,String type,String identity) throws SQLException{
		if("3".equals(identity)){
			String sql = "SELECT b.s_name as schoolName,b.p_id as schoolId, c.s_name as itemName,c.p_id as itemId, d.s_name as type " +
                    " FROM t_expert_item_map as a left join t_school as b on a.s_school_id = b.p_id left join t_index_target " +
                    " as c on a.s_item_id = c.p_id left join t_school_type as d on b.p_type_id = d.p_id where a.s_type=? " +
                    " and  a.s_expert_id  in " +
                    " (SELECT ei.s_expert_id from t_expert_group_map ei WHERE s_group_id in " +
                    " (SELECT egm.s_group_id from t_expert_group_map egm  left JOIN t_group g ON g.p_id=egm.s_group_id where egm.s_expert_id= ? AND g.s_state=1))";
			return dbHelper.getBeanList(sql, ItemListExpertBean.class,type,expertId);
		}else {
			String sql = "SELECT b.s_name as schoolName,b.p_id as schoolId, c.s_name as itemName,c.p_id as itemId, d.s_name as type FROM t_expert_item_map as a"
					+ " left join t_school as b on a.s_school_id = b.p_id left join t_index_target as c on a.s_item_id = c.p_id left join t_school_type as d on b.p_type_id = d.p_id where a.s_expert_id = ? and a.s_type=? ";
			return dbHelper.getBeanList(sql, ItemListExpertBean.class, expertId, type);
		}
	}

	public void deleteBySchoolIdAndExpertId(String schoolId,String expertId,String type) throws SQLException{
			String sql = "delete from  t_expert_item_map where s_school_id =? and s_expert_id = ? and s_type=?";
			this.dbHelper.execute(sql,schoolId,expertId,type);
		}

	public List<ItemListExpertBean> groupBySchoolId(String expertId, String type) throws SQLException {
		String sql = "SELECT a.s_school_id as schoolId FROM t_expert_item_map a " +
				"where a.s_expert_id = ? and a.s_type=? group by a.s_school_id ";
		return dbHelper.getBeanList(sql, ItemListExpertBean.class, expertId,type);
	}
}
